package org.mparts.server;

import java.io.IOException;
import java.nio.channels.Channels;
import java.text.NumberFormat;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.logging.Level;
import java.util.logging.Logger;

import javax.persistence.PersistenceException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.mparts.server.domain.Auto;
import org.mparts.server.domain.Engine;
import org.mparts.server.domain.Model;
import org.mparts.server.domain.Partgroup;
import org.mparts.server.domain.Partitem;
import org.mparts.server.service.MpartService;
import org.mparts.server.utils.CacheUtils;
import org.mparts.server.utils.PersistUtils;

import jxl.Cell;
import jxl.CellType;
import jxl.NumberCell;
import jxl.Sheet;
import jxl.Workbook;

import com.google.appengine.api.blobstore.BlobstoreService;
import com.google.appengine.api.blobstore.BlobstoreServiceFactory;
import com.google.appengine.api.blobstore.BlobKey;
import com.google.appengine.api.files.AppEngineFile;
import com.google.appengine.api.files.FileReadChannel;
import com.google.appengine.api.files.FileService;
import com.google.appengine.api.files.FileServiceFactory;

public class UploadServlet extends HttpServlet {
	/**
	 * 
	 */
	private static final long serialVersionUID = 3897538693138966878L;

	private BlobstoreService blobstoreService = BlobstoreServiceFactory
			.getBlobstoreService();

	private static final Logger log = Logger.getLogger(UploadServlet.class
			.getName());

	public static final String KEY_LOAD_CATALOG = "_LOAD_CATALOG_RESULT_";

	private enum Columns {
		CNT, GRP, AUTO, MODEL, MANYEAR, NUM, ENGINE, DRIVE, NOTE, CODE, PRICE
	}

	public UploadServlet() {
	}

	@Override
	protected void doPost(HttpServletRequest req, HttpServletResponse resp)
			throws ServletException, IOException {
		log.warning("start upload...");
		Map<String, List<BlobKey>> blobs = blobstoreService.getUploads(req);
		List<BlobKey> blobKeyList = blobs.get("uploadedfile");

		if (blobKeyList == null) {
			log.warning("blobKeyList is null, upload failed");
			resp.sendRedirect("/");
		} else {
			if (blobKeyList.size() > 0) {
				BlobKey blobKey = blobKeyList.get(0);
				FileService fs = FileServiceFactory.getFileService();
				AppEngineFile file = fs.getBlobFile(blobKey);
				FileReadChannel readChannel = fs.openReadChannel(file, false);

				// Again, different standard Java ways of reading from the
				// channel.
				// EntityManager mgr = EMF.get().createEntityManager();
				// try {
				// Query q = mgr.createQuery("select x from Partgroup x",
				// Partgroup.class);
				// partGroupList = q.getResultList();
				// } finally {
				// mgr.close();
				// }

				Workbook workBook = null;
				try {
					log.warning("start read Excel file...");
					workBook = Workbook.getWorkbook(Channels
							.newInputStream(readChannel));
					Long storeId = 1L;
					try {
						storeId = new Long(req.getParameter("store"));
					} catch (Exception e) {
						// TODO: handle exception
						log.log(Level.SEVERE, "failed parameter 'store' parse", e);
					}
					processWorkbook(workBook, storeId);
					workBook.close();
				} catch (Exception e) {
					log.log(Level.SEVERE, "Failed process workbook", e);
					log.warning("Put to session error message: " + e.getMessage());
					req.getSession().setAttribute(KEY_LOAD_CATALOG,
							"ERROR: " + e.getMessage());
				} finally {
					try {
						if (workBook != null) {
							log.warning("Close workbook");
							workBook.close();
						}
					} catch (Exception e) {
						log.severe("Closing workbook failed");
					}
					log.warning("delete blobkey from blobstore");
					blobstoreService.delete(blobKey);
					log.warning("remove all cache keys");
					CacheUtils.removeAll();
					try {
						readChannel.close();
					} catch (Exception e2) {
						log.warning("Failed close read channel");
					}
				}
				// resp.sendRedirect("/serve?blob-key=" +
				// blobKey.getKeyString());
			} else {
				resp.sendRedirect("/");
			}
		}
	}

	private void processWorkbook(Workbook workbook, Long storeId) throws Exception {
		List<UploadServlet.Columns> colList = Arrays.asList(Columns.values());
		MpartService mpartService = new MpartService();

		Sheet sheet = workbook.getSheet(0);
		int count = sheet.getRows();
		log.warning("Start processing [" + count + "] rows");
		
		boolean start = false;
		int i = 0; // row counter
		try {
			PersistUtils.startTransaction();
			if (count > 0) {
				mpartService.clearCatalog(storeId);
			}

			List<Partgroup> partGroupList = mpartService.findAllPartgroups();
			List<Auto> autoList = mpartService.findAutos();
			List<Partitem> partItemList = mpartService.findAllPartitems();
			List<Engine> engineList = mpartService.findAllEngines();
			HashMap<String, Partitem> partItemMap = new HashMap<String, Partitem>();

			for (Partitem item : partItemList) {
				partItemMap.put(item.getCode(), item);
			}

			for (i = 0; i < count; i++) {
				Cell[] row = sheet.getRow(i);
				log.info("get item code");
				String itemCode = getCellContent(row,
						colList.indexOf(Columns.CODE));
				if (!start) {
					if (row.length > 0 && "Инв. номер".equals(itemCode)) {
						start = true;
					}
					continue;
				}

				if (itemCode.length() == 0) {
					log.warning("Row [" + i + "] has empty code");
					continue;
					//itemCode = "-";
				}

				Partitem p = partItemMap.get(itemCode);
				if (p == null) {
					p = new Partitem();
				}

				if (p.getOrderId() != null && p.getOrderId() > 0) {
					log.severe("part item [" + itemCode + "] already ordered ["
							+ p.getOrderId() + "] and unmodified");
					continue;
				}

				// ��� ������
				p.setCode(itemCode);

				// ������������ ������
				log.info("get item grp");
				String grp = getCellContent(row, colList.indexOf(Columns.GRP));
				boolean found = false;
				for (Partgroup item : partGroupList) {
					if (item.getName().equals(grp)) {
						p.setGrp(item.getId());
						p.setGrpName(grp);
						found = true;
						break;
					}
				}
				if (!found) {
					Partgroup newGrp = new Partgroup();
					newGrp.setName(grp);
					newGrp = PersistUtils.mergeObject(newGrp, false);
					log.warning("partgroup id = " + newGrp.getId());
					partGroupList.add(newGrp);
					p.setGrp(newGrp.getId());
					p.setGrpName(grp);
				}

				// ����
				log.info("get item price");
				NumberFormat nf = NumberFormat.getIntegerInstance();
				String price = getCellContent(row,
						colList.indexOf(Columns.PRICE), nf);
				try {
					p.setPrice(nf.parse(price).intValue());
				} catch (Exception e) {
					log.severe("incorrect price value: " + price);
					p.setPrice(0);
				}

				// ����� � ������
				log.info("get item auto & model");
				String auto = getCellContent(row, colList.indexOf(Columns.AUTO))
						.toUpperCase();
				String model = getCellContent(row,
						colList.indexOf(Columns.MODEL)).toUpperCase();
				if (auto.length() == 0) {
					auto = "-";
				}
				if (model.length() == 0) {
					model = "-";
				}
				log.info("process auto: " + auto + "[" + model + "]");

				found = false;
				Auto aFound = null;
				for (Auto item : autoList) {
					if (auto.equals(item.getName())) {
						for (Model mdl : item.getModels()) {
							if (model.equals(mdl.getName())) {
								p.setAuto(item.getId());
								p.setModel(mdl.getId());
								p.setAutoName(auto);
								p.setModelName(model);
								found = true;
								break;
							}
						}
						if (found) {
							break;
						} else {
							aFound = item;
							break;
						}
					}
				}
				if (!found) {
					if (aFound == null) {
						aFound = new Auto();
						aFound.setName(auto);
//						PersistUtils.mergeObject(aFound, false);
						// aFound.setModels(new HashSet<Model>());
//						autoList.add(aFound);
					}
					int index = autoList.indexOf(aFound);
					if ( index != -1 ) {
						autoList.remove(index);
					}
					Model mdl = new Model();
					mdl.setName(model);
					aFound.addModel(mdl);
					// mdl.setAuto(aFound);
					aFound = PersistUtils.mergeObject(aFound, false);
					for ( Model m2 :aFound.getModels() ) {
						if (m2.getName().equals(model)) {
							mdl = m2;
							break;
						}
					}
					autoList.add(aFound);
					
					log.warning("auto id = " + aFound.getId() + ", model id = " + mdl.getId());

//					PersistUtils.mergeObject(mdl, false);
//					autoList.add(aFound);
					p.setAuto(aFound.getId());
					p.setModel(mdl.getId());
					p.setAutoName(auto);
					p.setModelName(model);
				}

				// привод
				log.info("get item drive");
				p.setDrive(getCellContent(row, colList.indexOf(Columns.DRIVE)));

				// номер кузова
				log.info("get item num");
				p.setNum(getCellContent(row, colList.indexOf(Columns.NUM)));

				// номер двигателя
				log.info("get item engine");
				p.setEngine(getCellContent(row, colList.indexOf(Columns.ENGINE)));

				// год выпуска
				log.info("get item manyear");
				p.setManDate(getCellContent(row,
						colList.indexOf(Columns.MANYEAR)));

				// примечание
				log.info("get item note");
				p.setNote(getCellContent(row, colList.indexOf(Columns.NOTE)));
				
				//set city name & store
				p.setStoreId(storeId);
				

				PersistUtils.mergeObject(p, false);
				
				boolean eFound = false;
				if (p.getEngine().length() > 0) {
					for (Engine engine : engineList) {
						eFound = p.getAuto() == engine.getAuto()
								&& p.getGrp() == engine.getPartgroup()
								&& p.getEngine().equalsIgnoreCase(
										engine.getName());
						if (eFound) {
							break;
						}
					}
				} else {
					eFound = true; // don't store empty
				}
				
				if ( !eFound ) { //create new engine
					Engine engine = new Engine();
					engine.setAuto(p.getAuto());
					engine.setPartgroup(p.getGrp());
					engine.setName(p.getEngine());
					engine = PersistUtils.mergeObject(engine, false);
					if ( engine != null ) {
						engineList.add(engine);
					}
				}

			}
			i = 0;
			PersistUtils.commitTransaction();
		} catch (Exception ex) {
			log.warning("Rollback transaction");
			PersistUtils.rollbackTransaction();
			String message = null;
			if ( i != 0 ) {
				message = "Processing row [" + i + "] failed";
			} else {
				message = "Something crashed...";
			}
			throw new Exception(message, ex);
		}
	}

	private String getCellContent(Cell[] row, int idx) {
		return getCellContent(row, idx, null);
	}

	private String getCellContent(Cell[] row, int idx, NumberFormat nf) {
		String result = "";
		if (idx >= row.length) {
			log.warning("trying to get too big cell number: " + idx);
		} else {
			if (row[idx].getType() == CellType.NUMBER && nf != null) {
				NumberCell cell = (NumberCell) row[idx];
				log.info("NUBMER cell:" + cell.getValue());
				result = nf.format(cell.getValue());
			} else {
				result = row[idx].getContents().trim();
			}
		}
		return result;
	}

}
